package com.btsk.dao.jdbc;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.cxf.common.i18n.Exception;
import org.hibernate.Query;
import org.hibernate.criterion.DetachedCriteria;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.security.concurrent.SessionAlreadyUsedException;

import com.btsk.dao.GpsInfoDao;
import com.btsk.model.GpsInfo;
import com.common.util.DateUtil;
import com.common.util.StringUtil;

public class GpsInfoMysqlJdbcDao extends JdbcDaoSupport implements GpsInfoDao {

	protected final Log log = LogFactory.getLog(getClass());

	private String dbData = "mmp_ms";

	private String dbHistory = "mmp_ms_history";

	private String queryMapToSql(Map<String, Object> queryMap) {
		String sql = "select c.id,c.car_no,c.color,e.name,f.corp_name,t.description,r.name from mmp_car_info c LEFT join  mmp_operate_platform e ON c.platform_id=e.id left join mmp_car_corp_info f on c.`corp_id`=f.id left join mmp_car_type t on c.car_type_id=t.id left join mmp_region r on c.region_id=r.id where 1=1 ";
		StringBuffer sb = new StringBuffer();
		sb.append("insert into VehicleInfo_TempTab(vehicle_id,veh_no_code,color,enterprise,corpName,carTypeName,earaName) ");
		sb.append(sql);
		Long enterpriseId = (Long) queryMap.get("enterpriseId");

		Long carCorpId = (Long) queryMap.get("carCorpId");

		String carNo = (String) queryMap.get("carNo");

		Integer color = (Integer) queryMap.get("color");

		Long regionId = (Long) queryMap.get("regionId");

		Long carTypeId = (Long) queryMap.get("carTypeId");

		Long carId = (Long) queryMap.get("carId");

		if (color != null && color >= 0) {
			sb.append(" and c.color=" + color);
		}

		if (carTypeId != null && carTypeId >= 0) {
			sb.append(" and c.car_type_id=" + carTypeId);
		}
		if (enterpriseId != null && enterpriseId >= 0) {
			sb.append(" and c.platform_id=" + enterpriseId);
		}

		if (carCorpId != null && carCorpId >= 0) {
			sb.append(" and c.corp_id=" + carCorpId);
		}

		if (regionId != null && regionId >= 0) {
			sb.append(" and c.region_id=" + regionId);
		}

		if (!StringUtil.isEmpty(carNo)) {
			sb.append(" and c.car_no = '" + carNo + "'");
		}

		if (carId != null) {
			sb.append(" and c.id = " + carId);
		}
		return sb.toString();
	}

	private Map<String, String> gpsInfoTableMap = new HashMap<String, String>();

	@Override
	public void saveGpsInfo(final GpsInfo gpsInfo) {

		String sql = "{call spr_add_gpsinfo (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
		// log.debug("sql=" + sql);

		// boolean exist = isGpsInfoTableExist(gpsInfo.getLocalTime());
		// while (!exist) {
		// if (exist == false) {
		// getJdbcTemplate().execute(
		// "{call spr_create_gpsinfo_table_test (?)}",
		// new PreparedStatementCallback() {
		//
		// @Override
		// public Object doInPreparedStatement(
		// PreparedStatement pstatment)
		// throws SQLException, DataAccessException {
		// pstatment.setTimestamp(1, new Timestamp(gpsInfo
		// .getLocalTime().getTime()));
		// return null;
		// }
		// });
		// }
		// exist = isGpsInfoTableExist(gpsInfo.getLocalTime());
		// }

//		getJdbcTemplate().execute(sql, new PreparedStatementCallback() {
//
//			@Override
//			public Object doInPreparedStatement(PreparedStatement pstatment)
//					throws SQLException, DataAccessException {
//				pstatment.setString(1, "sa");
//				pstatment.setLong(2, gpsInfo.getCarId());
//				pstatment.setInt(3, gpsInfo.getType());
//				pstatment.setInt(4, gpsInfo.getSubType());
//				pstatment.setInt(5, gpsInfo.isValid() ? 1 : 0);
//				pstatment.setTimestamp(6, new Timestamp(gpsInfo.getGpsTime()
//						.getTime()));
//				pstatment.setTimestamp(7, new Timestamp(gpsInfo.getLocalTime()
//						.getTime()));
//				pstatment.setDouble(8, gpsInfo.getLat());
//				pstatment.setDouble(9, gpsInfo.getLon());
//				pstatment.setLong(10, gpsInfo.getAngle());
//				pstatment.setInt(11, gpsInfo.getHeight());
//				pstatment.setInt(12, gpsInfo.getSpeed());
//				pstatment.setInt(13, gpsInfo.getLoading());
//				pstatment.setInt(14, gpsInfo.getStatus1());
//				pstatment.setInt(15, gpsInfo.getStatus2());
//				pstatment.setInt(16, gpsInfo.getStatus3());
//				pstatment.setInt(17, gpsInfo.getStation());
//				pstatment.setInt(18, gpsInfo.getCell());
//				return pstatment.execute();
//			}
//		});
	}



	@Override
	public int findGpsInfoCount(final String queryStr, final Date startTime,
			final Date endTime, final int opr, final int type,
			final int subType, final int valid, final int minSpeed,
			final int maxSpeed) {

		final String sql = "{call spr_gpsinfo_query_by_querystr(?,?,?,?,?,?,?,?,?,?,?,?)}";
		if (!isExistGpsTable(startTime)) {
			return 0;
		}
		final List<Integer> list = new ArrayList<Integer>();
		getJdbcTemplate().execute(sql, new PreparedStatementCallback() {
			@Override
			public Object doInPreparedStatement(PreparedStatement pstatment)
					throws SQLException, DataAccessException {
				pstatment.setString(1, "sa");
				pstatment.setTimestamp(2, new Timestamp(startTime.getTime()));
				pstatment.setTimestamp(3, new Timestamp(endTime.getTime()));
				pstatment.setString(4, queryStr);
				pstatment.setInt(5, 0);
				pstatment.setInt(6, type);
				pstatment.setInt(7, subType);
				pstatment.setInt(8, valid);
				pstatment.setInt(9, minSpeed);
				pstatment.setInt(10, maxSpeed);
				pstatment.setInt(11, 0);
				pstatment.setInt(12, 0);
				ResultSet rs = pstatment.executeQuery();
				if (rs.next()) {
					list.add(rs.getInt(1));
				}
				return true;
			}
		});

		if (list.size() > 0) {
			return list.get(0);
		} else {
			return 0;
		}
	}


	@Override
	public int findGpsInfoCount(Map<String, Object> queryMap, Date startTime,
			Date endTime, int opr, int type, int subType, int valid,
			int minSpeed, int maxSpeed) {
		return findGpsInfoCount(queryMapToSql(queryMap), startTime, endTime,
				opr, type, subType, valid, minSpeed, maxSpeed);
	}


	public void saveBathNewGpsInfo(List<GpsInfo> gpsInfos) {
		if (gpsInfos.size() < 1) {
			return;
		}
		StringBuffer sb = new StringBuffer();
		int total = gpsInfos.size();
		int n = 0;
		StringBuffer sb1 = new StringBuffer();
		sb1.append("delete from NewMessage where DeviceNo in (");
		for (GpsInfo gpsInfo : gpsInfos) {
			sb1.append("'" + gpsInfo.getDeviceNo() + "'");
			n++;
			sb.append(createInsertSql(gpsInfo, "NewMessage"));
			if (n == total) {
				sb1.append(");");
			} else {
				sb1.append(",");
			}
		}
		// log.info("sql1 = " + sb1.toString());
		// log.info("sql2 = " + sb.toString());
		String[] sqls = new String[2];
		sqls[0] = sb1.toString();
		sqls[1] = sb.toString();
		getJdbcTemplate().batchUpdate(sqls);
	}

	@Override
	public void saveNewGpsInfo(final GpsInfo gpsInfo) {

//		String sql = "{call spr_save_new_gpsinfo (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
//		log.debug("sql=" + sql);
//		getJdbcTemplate().execute(sql, new PreparedStatementCallback() {
//
//			@Override
//			public Object doInPreparedStatement(PreparedStatement pstatment)
//					throws SQLException, DataAccessException {
//				pstatment.setString(1, "sa");
//				pstatment.setLong(2, gpsInfo.getCarId());
//				pstatment.setInt(3, gpsInfo.getType());
//				pstatment.setInt(4, gpsInfo.getSubType());
//				pstatment.setInt(5, gpsInfo.isValid() ? 1 : 0);
//				pstatment.setTimestamp(6, new Timestamp(gpsInfo.getGpsTime()
//						.getTime()));
//				pstatment.setTimestamp(7, new Timestamp(gpsInfo.getLocalTime()
//						.getTime()));
//				pstatment.setDouble(8, gpsInfo.getLat());
//				pstatment.setDouble(9, gpsInfo.getLon());
//				pstatment.setLong(10, gpsInfo.getAngle());
//				pstatment.setInt(11, gpsInfo.getHeight());
//				pstatment.setInt(12, gpsInfo.getSpeed());
//				pstatment.setInt(13, gpsInfo.getLoading());
//				pstatment.setInt(14, gpsInfo.getStatus1());
//				pstatment.setInt(15, gpsInfo.getStatus2());
//				pstatment.setInt(16, gpsInfo.getStatus3());
//				pstatment.setInt(17, gpsInfo.getStation());
//				pstatment.setInt(18, gpsInfo.getCell());
//				pstatment.setInt(19, gpsInfo.getCourse());
//				pstatment.setInt(20, gpsInfo.getVelocity());
//				pstatment.setInt(21, gpsInfo.isEncrypt() ? 1 : 0);
//				return pstatment.execute();
//			}
//		});
	}

	@Override
	public synchronized void saveGpsTable(final Date localTime) {
		if (isExistGpsTable(localTime)) {
			return;
		}
		String sql = "{call spr_create_gpsinfo_table (?)}";
		// log.debug("sql=" + sql);
		getJdbcTemplate().execute("{call spr_create_gpsinfo_table (?)}",
				new PreparedStatementCallback() {
					@Override
					public Object doInPreparedStatement(
							PreparedStatement pstatment) throws SQLException,
							DataAccessException {
						pstatment.setTimestamp(1,
								new Timestamp(localTime.getTime()));
						return pstatment.execute();
					}
				});
	}

	@SuppressWarnings("unchecked")
	public boolean isExistGpsTable(Date localTime) {

		if (System.currentTimeMillis() < localTime.getTime()) {
			return false;
		}

		String sql = "select * from information_schema.TABLES WHERE  TABLE_SCHEMA ='"
				+ dbData
				+ "' and table_name = 'mmp_gps_info_"
				+ DateUtil.formateTime(localTime, "yyyyMMdd") + "';";
		// log.debug("sql=" + sql);

		List<Integer> list = getJdbcTemplate().query(sql, new RowMapper() {
			@Override
			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				return 1;
			}
		});

		if (list.size() > 0) {

			return true;
		}
		return false;
	}
	
	private String createInsertSql(GpsInfo gpsInfo, String tableName){
		StringBuffer sb = new StringBuffer();
		sb.append("insert into " + tableName);
		sb.append("(");
		sb.append("DeviceNo,");
		sb.append("Longitude,");
		sb.append("Latitude,");
		sb.append("ReceiveTime,");
		sb.append("Speed,");
		sb.append("Direction,");
		sb.append("Mileage,");
		sb.append("Status,");
		sb.append("EventCode,");
		sb.append("AlarmType,");
		sb.append("Alarm,");
		sb.append("AccStatus,");
		sb.append("LeftLight,");
		sb.append("RightLight,");
		sb.append("Brake,");
		sb.append("Fuel,");
		sb.append("TurnStatus,");
		sb.append("Temperature,");
		sb.append("DoorStatus");
		sb.append(")values(");
		
		sb.append("'" + gpsInfo.getDeviceNo() + "',");
		sb.append(gpsInfo.getLongitude() + ",");
		sb.append(gpsInfo.getLatitude() + ",");
		sb.append("'" + DateUtil.formateTime(gpsInfo.getReceiveTime(), "yyyy-MM-dd HH:mm:ss") + "',");
		sb.append(gpsInfo.getSpeed() + ",");
		sb.append(gpsInfo.getDirection() + ",");
		sb.append(gpsInfo.getMileage() + ",");
		sb.append("'" + gpsInfo.getStatus() + "',");
		sb.append("'" + gpsInfo.getEventCode() + "',");
		sb.append("'" + gpsInfo.getAlarmType() + "',");
		sb.append(gpsInfo.getAlarm() + ",");
		sb.append(gpsInfo.getAccStatus() + ",");
		sb.append(gpsInfo.getLeftLight() + ",");
		sb.append(gpsInfo.getRightLight() + ",");
		sb.append(gpsInfo.getBrake() + ",");
		sb.append("'" + gpsInfo.getFuel() + "',");
		sb.append(gpsInfo.getTurnStatus() + ",");
		sb.append("'" + gpsInfo.getTemperature() + "',");
		sb.append(gpsInfo.getDoorStatus());
		
		sb.append(");");
		return sb.toString();
	}
	
	
	@Override
	public void saveBatchGpsInfo(String deviceNo, List<GpsInfo> gpsInfos) {
		if (gpsInfos.size() < 1) {
			return;
		}
		StringBuffer sb = new StringBuffer();
		for (GpsInfo gpsInfo : gpsInfos) {
			sb.append(createInsertSql(gpsInfo, "Z" + gpsInfo.getDeviceNo()));
		}
		String sql = sb.toString();
		getJdbcTemplate().execute(sql);
	}

	public void setDbData(String dbData) {
		this.dbData = dbData;
	}

	public void setDbHistory(String dbHistory) {
		this.dbHistory = dbHistory;
	}


	@Override
	public void removeCarInfo(final long car_id) {
		final String sql = "{call sp_delete_carinfo(?)}";
		// log.debug("sql=" + sql);
		getJdbcTemplate().execute(sql, new PreparedStatementCallback() {
			@Override
			public Object doInPreparedStatement(PreparedStatement pstatment)
					throws SQLException, DataAccessException {
				pstatment.setLong(1, car_id);
				return pstatment.executeQuery();
			}
		});
	}
}
